IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ag_showdup]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[usp_ag_showdup]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
	usp_ag_showdup: Show duplicate references (or store them into the #Duplicates temporary table, which then needs to be created by the caller)

	Copyright 2010 by Alberto Gemin
	agemin@hotmail.com
	Version 1.1
	30 Jul 2011

	If this procedure is called with @CreateTempTable = 0, the caller needs to create the #Duplicates temporary table beforehand with a statement
	similar to the following:

		CREATE TABLE #Duplicates (
			Id INTEGER IDENTITY(1,1) NOT NULL,	-- Primary Key
			RuleId UNIQUEIDENTIFIER,			-- Rule Id (FK to DuplicateRule)
			BaseEntityTypeCode INTEGER,			-- ObjectTypeCode of the base entity
			BaseObjectId UNIQUEIDENTIFIER,		-- Base Entity Id (FK to the base entity table)
			MatchingEntityTypeCode INTEGER,		-- ObjectTypeCode of the matching entity
			MatchingObjectId UNIQUEIDENTIFIER,	-- Matching Entity Id (FK to the matching entity table)
			Matchcode NVARCHAR(450),			-- Matchcode that was used for duplicate detection
		)

	In the above, "base entity" refers to the entity for which duplicates are detected/processed, while "matching entity" is the duplicate.
	Usually this is the same entity as the base entity, but in CRM 2011 one can deduplicate across different entities, for example contacts
	againts accounts.

	Note that because this procedure accesses low level objects, it must be executed by a system administrator. A normal CRM user will not be able
	to execute it.

*/
CREATE PROCEDURE [dbo].[usp_ag_showdup] (
	@EntityName NVARCHAR(160),				-- Base Entity (i.e. 'Contact' or 'Account' or similar)
	@PurgeResults BIT = 1,				    -- Whether to invoke or not usp_ag_showdup_purge (see)
	@CreateTempTable BIT = 1)				-- If this parameter is 0, the procedure will not create the table #Duplicates but assume it is created by the caller
AS
BEGIN
	SET NOCOUNT ON

	-- Create temporary table if needed
	IF @CreateTempTable = 1
		CREATE TABLE #Duplicates (
			Id INTEGER IDENTITY(1,1) NOT NULL,	-- Primary Key
			RuleId UNIQUEIDENTIFIER,			-- Rule Id (FK to DuplicateRule)
			BaseEntityTypeCode INTEGER,			-- ObjectTypeCode of the base entity
			BaseObjectId UNIQUEIDENTIFIER,		-- Base Entity Id (FK to the base entity table)
			MatchingEntityTypeCode INTEGER,		-- ObjectTypeCode of the matching entity
			MatchingObjectId UNIQUEIDENTIFIER,	-- Matching Entity Id (FK to the matching entity table)
			Matchcode NVARCHAR(450),			-- Matchcode that was used for duplicate detection
		)
	
	EXECUTE [dbo].[usp_ag_showdup_collect] @EntityName

	-- Purge record if needed
	IF @PurgeResults = 1 
	BEGIN
		IF @CreateTempTable = 1
			-- Before purging we create the index to help the purge query
			CREATE INDEX IX_#Duplicates on #Duplicates(MatchingObjectId, BaseObjectId, MatchingEntityTypeCode, BaseEntityTypeCode)

		EXECUTE [dbo].[usp_ag_showdup_purge]
	END

	-- If #Duplicates is not persistent we need to show the results
	IF @CreateTempTable = 1
		SELECT
			Id,
			RuleId,
			BaseEntityTypeCode,
			BaseObjectId,
			MatchingEntityTypeCode,
			MatchingObjectId,
			Matchcode
		FROM
			#Duplicates

END
GO
